﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Data.OleDb;
using System.Data.OracleClient;
using Dominio;

namespace Persistencia
{
    public class PtdnDAO
    {
        protected int _count = 0;
        protected SqlConnection conn = null;
        protected void closeConexao()
        {
            GerenciadorConexao.closeConexao(conn);
            conn = null;
        }
        protected void abrirConexao()
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
        }
        public int selectCountAll()
        {
            return _count;
        }
        #region métodos auxiliares
        private String getNomeTabela()
        {
            return "PTDN";
        }
        private String getSelect()
        {
            //no momento está inutilizavel
            return "select * " +
            "from " + this.getNomeTabela();
        }

        protected DbParameter novoParametro(String nome, DbType tipo, Object valor)
        {
            SqlParameter par = new SqlParameter();
            par.ParameterName = nome;
            if (tipo != null)
                par.DbType = tipo;
            if (valor != null)
                par.Value = valor;
            return par;

        }
        private DisciplinaVO getEntidadeDisciplina(DbDataReader dr)
        {
            DisciplinaVO disciplina = new DisciplinaVO();

            try
            {
                disciplina.id = (int)dr["id"];
                disciplina.Nome = (string)dr["nome"].ToString().Trim();               
            }
            catch (Exception)
            {
            }
            return disciplina;
        }
        #endregion
        #region métodos acessadores
        public List<DisciplinaVO> RetornarDisciplinasProf(int id_professor)
        {
            List<DisciplinaVO> disciplina = new List<DisciplinaVO>();
            SqlCommand command = new SqlCommand();
            conn = GerenciadorConexao.getConexao();
            command.CommandText = "Select Disciplina.* from Disciplina, " + this.getNomeTabela() + " where " + this.getNomeTabela() + ".ID_professor = @id_professor and "+this.getNomeTabela()+".ID_Disciplina = Disciplina.ID;";
            command.Connection = conn;            
            command.Parameters.Add(this.novoParametro("id_professor", DbType.Int32, id_professor));
             try
                {
                    abrirConexao();                   
                    DbDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                           disciplina.Add(this.getEntidadeDisciplina(dr));
                        }
                }
                else
                {
                    //não há registros
                }
                dr.Close();

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    closeConexao();
                }
            return disciplina;
            
            }
           
        
        #endregion        
        #region métodos modificadores
        public int inserirDisciplinaProfessor(List<DisciplinaVO> dvo, int ProfessorID)
        {
            int retorno = 0;
            SqlCommand command;

            foreach (DisciplinaVO disciplina in dvo)
            {
                conn = GerenciadorConexao.getConexao();
                command = new SqlCommand();
                command.CommandText = "INSERT INTO " + this.getNomeTabela() + " (ID_Professor, ID_Disciplina) VALUES (@id_professor, @id_disciplina)";
                command.Connection = conn;
                command.Parameters.Add(this.novoParametro("id_disciplina", DbType.Int32, disciplina.id));
                command.Parameters.Add(this.novoParametro("id_professor", DbType.Int32, ProfessorID));
                try
                {
                    if (conn == null)
                        conn = GerenciadorConexao.getConexao();
                    abrirConexao();
                    retorno = command.ExecuteNonQuery();

                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    closeConexao();
                }

                
            }
            return retorno;

           
        }
        public virtual int alterarDisciplinaProfessor(List<DisciplinaVO> dvo, int ProfessorID)
        {
            //Para alterar, primeiro ele apagará todos os dados da tabela PTDN com o ID do professor, depois ele insere de novo;
            int retorno = 0;
            conn = GerenciadorConexao.getConexao();

            SqlCommand command = new SqlCommand();
            command.CommandText = "Delete from " + this.getNomeTabela() + " where ID_Professor = @id_professor ";
            command.Connection = conn;
            command.Parameters.Add(this.novoParametro("id_professor", DbType.Int32, ProfessorID));          
            try
            {
                abrirConexao();
                retorno = command.ExecuteNonQuery();
                
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConexao();
            }

            retorno = inserirDisciplinaProfessor(dvo, ProfessorID);
            return retorno;

            
        }

        
        #endregion
    }
}
